const express = require("express")
const router = express.Router()
const StudentDB = require("../db/index")
const multer = require("multer")
const XLSX = require("xlsx")
const path = require("path")
const fs = require("fs")

// 配置文件上传
const upload = multer({
    dest: 'uploads/',
    fileFilter: (req, file, cb) => {
        const allowedTypes = [
            'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'application/vnd.ms-excel'
        ]
        if (allowedTypes.includes(file.mimetype)) {
            cb(null, true)
        } else {
            cb(new Error('只支持Excel文件格式(.xlsx, .xls)'))
        }
    },
    limits: {
        fileSize: 5 * 1024 * 1024 // 5MB
    }
})

router.get("/list", async(req, res) => {
    try {
        const page = parseInt(req.query.page) || 1
        const limit = parseInt(req.query.limit) || 10
        const search = req.query.search || ""
        const gender = req.query.gender || ""
        const major = req.query.major || ""

        const offset = (page - 1) * limit

        const result = await StudentDB.getStudentsWithPagination({
            search,
            gender,
            major,
            offset,
            limit
        })

        const totalPages = Math.ceil(result.total / limit)

        // 获取所有专业用于筛选下拉框
        const majors = await StudentDB.getAllMajors()

        res.render("index", {
            title: "学生信息管理系统",
            students: result.students,
            currentPage: page,
            totalPages,
            limit,
            search,
            gender,
            major,
            majors,
            total: result.total,
            message: req.query.message || null,
        })
    } catch (error) {
        console.error("获取学生列表失败:", error)
        res.render("index", {
            title: "学生信息管理系统",
            students: [],
            currentPage: 1,
            totalPages: 1,
            limit: 10,
            search: "",
            gender: "",
            major: "",
            majors: [],
            total: 0,
            error: "获取学生列表失败",
        })
    }
})


router.get("/add", (req, res) => {
    res.render("add", {
        title: "添加学生",
        student: {},
        errors: {},
    })
})


router.post("/add", async(req, res) => {
    const { name, age, gender, major, score } = req.body


    const errors = {}
    if (!name || name.trim() === "") {
        errors.name = "姓名不能为空"
    }
    if (!age || isNaN(age) || age < 1 || age > 150) {
        errors.age = "请输入有效的年龄"
    }
    if (!gender) {
        errors.gender = "请选择性别"
    }
    if (!major || major.trim() === "") {
        errors.major = "专业不能为空"
    }
    if (!score || isNaN(score) || score < 0 || score > 100) {
        errors.score = "请输入有效的成绩(0-100)"
    }

    if (Object.keys(errors).length > 0) {
        return res.render("add", {
            title: "添加学生",
            student: req.body,
            errors: errors,
        })
    }

    try {
        await StudentDB.addStudent({
            name: name.trim(),
            age: Number.parseInt(age),
            gender,
            major: major.trim(),
            score: Number.parseFloat(score),
        })
        res.redirect("/student/list?message=添加学生成功")
    } catch (error) {
        console.error("添加学生失败:", error)
        res.render("add", {
            title: "添加学生",
            student: req.body,
            errors: { general: "添加学生失败，请重试" },
        })
    }
})


router.get("/delete/:id", async(req, res) => {
    const studentId = req.params.id

    if (!studentId || isNaN(studentId)) {
        return res.redirect("/student/list?message=无效的学生ID")
    }

    try {
        const result = await StudentDB.deleteStudent(studentId)
        if (result.affectedRows > 0) {
            res.redirect("/student/list?message=删除学生成功")
        } else {
            res.redirect("/student/list?message=未找到要删除的学生")
        }
    } catch (error) {
        console.error("删除学生失败:", error)
        res.redirect("/student/list?message=删除学生失败")
    }
})


router.get("/edit/:id", async(req, res) => {
    const studentId = req.params.id

    if (!studentId || isNaN(studentId)) {
        return res.redirect("/student/list?message=无效的学生ID")
    }

    try {
        const student = await StudentDB.getStudentById(studentId)
        if (!student) {
            return res.redirect("/student/list?message=未找到要编辑的学生")
        }

        res.render("edit", {
            title: "编辑学生信息",
            student: student,
            errors: {},
        })
    } catch (error) {
        console.error("获取学生信息失败:", error)
        res.redirect("/student/list?message=获取学生信息失败")
    }
})


router.post("/update", async(req, res) => {
    const { id, name, age, gender, major, score } = req.body

    if (!id || isNaN(id)) {
        return res.redirect("/student/list?message=无效的学生ID")
    }


    const errors = {}
    if (!name || name.trim() === "") {
        errors.name = "姓名不能为空"
    }
    if (!age || isNaN(age) || age < 1 || age > 150) {
        errors.age = "请输入有效的年龄"
    }
    if (!gender) {
        errors.gender = "请选择性别"
    }
    if (!major || major.trim() === "") {
        errors.major = "专业不能为空"
    }
    if (!score || isNaN(score) || score < 0 || score > 100) {
        errors.score = "请输入有效的成绩(0-100)"
    }

    if (Object.keys(errors).length > 0) {
        return res.render("edit", {
            title: "编辑学生信息",
            student: req.body,
            errors: errors,
        })
    }

    try {
        const result = await StudentDB.updateStudent(id, {
            name: name.trim(),
            age: Number.parseInt(age),
            gender,
            major: major.trim(),
            score: Number.parseFloat(score),
        })

        if (result.affectedRows > 0) {
            res.redirect("/student/list?message=更新学生信息成功")
        } else {
            res.redirect("/student/list?message=未找到要更新的学生")
        }
    } catch (error) {
        console.error("更新学生信息失败:", error)
        res.render("edit", {
            title: "编辑学生信息",
            student: req.body,
            errors: { general: "更新学生信息失败，请重试" },
        })
    }
})

// 统计页面
router.get("/statistics", async(req, res) => {
    try {
        const stats = await StudentDB.getStatistics()
        res.render("statistics", {
            title: "学生数据统计",
            stats
        })
    } catch (error) {
        console.error("获取统计数据失败:", error)
        res.render("statistics", {
            title: "学生数据统计",
            stats: null,
            error: "获取统计数据失败"
        })
    }
})

// 批量删除
router.post("/batch-delete", async(req, res) => {
    const { studentIds } = req.body

    if (!studentIds || !Array.isArray(studentIds) || studentIds.length === 0) {
        return res.redirect("/student/list?message=请选择要删除的学生")
    }

    try {
        const result = await StudentDB.batchDeleteStudents(studentIds)
        res.redirect(`/student/list?message=成功删除${result.affectedRows}个学生`)
    } catch (error) {
        console.error("批量删除学生失败:", error)
        res.redirect("/student/list?message=批量删除失败")
    }
})

// 数据导出 - Excel格式
router.get("/export", async(req, res) => {
    try {
        const students = await StudentDB.getAllStudents()

        // 创建工作簿
        const wb = XLSX.utils.book_new()

        // 准备数据
        const data = students.map(student => ({
            'ID': student.id,
            '姓名': student.name,
            '年龄': student.age,
            '性别': student.gender,
            '专业': student.major,
            '成绩': student.score,
            '创建时间': student.created_at,
            '更新时间': student.updated_at
        }))

        // 创建工作表
        const ws = XLSX.utils.json_to_sheet(data)

        // 设置列宽
        ws['!cols'] = [
            { wch: 8 }, // ID
            { wch: 15 }, // 姓名
            { wch: 8 }, // 年龄
            { wch: 8 }, // 性别
            { wch: 20 }, // 专业
            { wch: 10 }, // 成绩
            { wch: 20 }, // 创建时间
            { wch: 20 } // 更新时间
        ]

        // 添加工作表到工作簿
        XLSX.utils.book_append_sheet(wb, ws, "学生信息")

        // 生成Excel文件
        const filename = `学生信息_${new Date().toISOString().slice(0, 10)}.xlsx`
        const filepath = path.join(__dirname, '..', 'temp', filename)

        // 确保temp目录存在
        const tempDir = path.join(__dirname, '..', 'temp')
        if (!fs.existsSync(tempDir)) {
            fs.mkdirSync(tempDir, { recursive: true })
        }

        XLSX.writeFile(wb, filepath)

        // 设置响应头并发送文件
        res.setHeader('Content-Disposition', `attachment; filename="${encodeURIComponent(filename)}"`)
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

        res.sendFile(filepath, (err) => {
            if (err) {
                console.error('发送文件失败:', err)
                res.status(500).send('文件下载失败')
            } else {
                // 删除临时文件
                fs.unlink(filepath, (unlinkErr) => {
                    if (unlinkErr) console.error('删除临时文件失败:', unlinkErr)
                })
            }
        })

    } catch (error) {
        console.error("导出数据失败:", error)
        res.redirect("/student/list?message=导出数据失败")
    }
})

// 数据导入页面
router.get("/import", (req, res) => {
    res.render("import", {
        title: "数据导入",
        message: req.query.message || null,
        error: req.query.error || null
    })
})

// 数据导入处理
router.post("/import", upload.single('excelFile'), async(req, res) => {
    if (!req.file) {
        return res.redirect("/student/import?error=请选择要导入的Excel文件")
    }

    try {
        // 读取Excel文件
        const workbook = XLSX.readFile(req.file.path)
        const sheetName = workbook.SheetNames[0]
        const worksheet = workbook.Sheets[sheetName]
        const data = XLSX.utils.sheet_to_json(worksheet)

        if (data.length === 0) {
            return res.redirect("/student/import?error=Excel文件为空或格式不正确")
        }

        let successCount = 0
        let errorCount = 0
        const errors = []

        // 验证和导入数据
        for (let i = 0; i < data.length; i++) {
            const row = data[i]
            const rowNum = i + 2 // Excel行号（从第2行开始，第1行是标题）

            try {
                // 数据验证
                const name = row['姓名'] || row['name']
                const age = parseInt(row['年龄'] || row['age'])
                const gender = row['性别'] || row['gender']
                const major = row['专业'] || row['major']
                const score = parseFloat(row['成绩'] || row['score'])

                if (!name || name.trim() === '') {
                    errors.push(`第${rowNum}行：姓名不能为空`)
                    errorCount++
                    continue
                }

                if (!age || isNaN(age) || age < 1 || age > 150) {
                    errors.push(`第${rowNum}行：年龄必须是1-150之间的数字`)
                    errorCount++
                    continue
                }

                if (!gender || !['男', '女'].includes(gender)) {
                    errors.push(`第${rowNum}行：性别必须是"男"或"女"`)
                    errorCount++
                    continue
                }

                if (!major || major.trim() === '') {
                    errors.push(`第${rowNum}行：专业不能为空`)
                    errorCount++
                    continue
                }

                if (!score || isNaN(score) || score < 0 || score > 100) {
                    errors.push(`第${rowNum}行：成绩必须是0-100之间的数字`)
                    errorCount++
                    continue
                }

                // 添加到数据库
                await StudentDB.addStudent({
                    name: name.trim(),
                    age,
                    gender,
                    major: major.trim(),
                    score
                })

                successCount++

            } catch (error) {
                console.error(`导入第${rowNum}行数据失败:`, error)
                errors.push(`第${rowNum}行：导入失败 - ${error.message}`)
                errorCount++
            }
        }

        // 删除上传的临时文件
        fs.unlink(req.file.path, (err) => {
            if (err) console.error('删除上传文件失败:', err)
        })

        // 返回结果
        if (successCount > 0 && errorCount === 0) {
            res.redirect(`/student/list?message=成功导入${successCount}条学生信息`)
        } else if (successCount > 0 && errorCount > 0) {
            res.redirect(`/student/import?message=成功导入${successCount}条，失败${errorCount}条&error=${encodeURIComponent(errors.slice(0, 5).join('; '))}`)
        } else {
            res.redirect(`/student/import?error=${encodeURIComponent(errors.slice(0, 5).join('; '))}`)
        }

    } catch (error) {
        console.error("导入数据失败:", error)

        // 删除上传的临时文件
        if (req.file) {
            fs.unlink(req.file.path, (err) => {
                if (err) console.error('删除上传文件失败:', err)
            })
        }

        res.redirect("/student/import?error=文件处理失败，请检查文件格式")
    }
})

// 下载导入模板
router.get("/template", (req, res) => {
    try {
        // 创建模板数据
        const templateData = [{
                '姓名': '张三',
                '年龄': 20,
                '性别': '男',
                '专业': '计算机科学与技术',
                '成绩': 85.5
            },
            {
                '姓名': '李四',
                '年龄': 19,
                '性别': '女',
                '专业': '软件工程',
                '成绩': 92.0
            }
        ]

        // 创建工作簿
        const wb = XLSX.utils.book_new()
        const ws = XLSX.utils.json_to_sheet(templateData)

        // 设置列宽
        ws['!cols'] = [
            { wch: 15 }, // 姓名
            { wch: 8 }, // 年龄
            { wch: 8 }, // 性别
            { wch: 20 }, // 专业
            { wch: 10 } // 成绩
        ]

        XLSX.utils.book_append_sheet(wb, ws, "学生信息模板")

        const filename = '学生信息导入模板.xlsx'
        const filepath = path.join(__dirname, '..', 'temp', filename)

        // 确保temp目录存在
        const tempDir = path.join(__dirname, '..', 'temp')
        if (!fs.existsSync(tempDir)) {
            fs.mkdirSync(tempDir, { recursive: true })
        }

        XLSX.writeFile(wb, filepath)

        res.setHeader('Content-Disposition', `attachment; filename="${encodeURIComponent(filename)}"`)
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

        res.sendFile(filepath, (err) => {
            if (err) {
                console.error('发送模板文件失败:', err)
                res.status(500).send('模板下载失败')
            } else {
                // 删除临时文件
                fs.unlink(filepath, (unlinkErr) => {
                    if (unlinkErr) console.error('删除临时文件失败:', unlinkErr)
                })
            }
        })

    } catch (error) {
        console.error("生成模板失败:", error)
        res.status(500).send('模板生成失败')
    }
})

module.exports = router